package models;

import models.admin.AdminUser;
import models.constants.DeletedStatus;
import models.merchant.Merchant;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import util.common.ConvertUtil;

import javax.persistence.*;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by liming on 16/7/25.
 * 出库单
 */
@Entity
@Table(name = "admin_stock_out")
public class AdminStockOut extends BaseModel {
    /**
     * 出单编号
     */
    @Column(name = "code")
    public String code;

    /**
     * 出库日期
     */
    @Column(name = "out_date")
    @Temporal(TemporalType.DATE)
    public Date outDate;

    /**
     * 总金额
     */
    @Column(name = "total_amount")
    public Double totalAmount = 0D;

    /**
     * 商户
     */
    @ManyToOne
    @JoinColumn(name = "merchant_id")
    public Merchant merchant;

    /**
     * 和订单保持一致
     * 已生成出库单 30,出库单提交 40,已出库 50
     */
    @Column(name = "status")
    public Integer status = 30;

    /**
     * 提交人
     */
    @ManyToOne
    @JoinColumn(name = "submit_user_id")
    public AdminUser submitUser;

    /**
     * 提交事件
     */
    @Column(name = "submit_date")
    @Temporal(TemporalType.TIMESTAMP)
    public Date submitDate;

    /**
     * 出库操作人员
     */
    @ManyToOne
    @JoinColumn(name = "out_opt_user_id")
    public AdminUser outOptUser;

    /**
     * 出库操作时间
     */
    @Column(name = "out_opt_date")
    @Temporal(TemporalType.TIMESTAMP)
    public Date outOptDate;

    /**
     * 出库单打印次数
     */
    @Column(name = "print_qty")
    public Integer printQty = 0;

    /**
     * 订单打印次数
     */
    @Column(name = "print_order_qty")
    public Integer printOrderQty = 0;

    /**
     * 备注
     */
    @Column(name = "remark")
    public String remark;

    /**
     * 版本号
     */
    @Version
    public Integer version = 0;

    /**
     * 删除状态
     */
    @Column(name = "deleted_status")
    @Enumerated(EnumType.ORDINAL)
    public DeletedStatus deletedStatus = DeletedStatus.UN_DELETED;

    /**
     * 出库操作拆分订单明细
     * @param adminStockOutId
     */
    public final static String SQL_STOCK_OUT_ORDER_ITEM_SPLITE =
            "insert into admin_order_item_splite (" +
            " cost_price,num,admin_order,admin_order_item_id,combo_product_spec_id,split_product_spec_id,sale_price,admin_stock_out_id" +
            ")" +
            "select" +
            " ifnull(b.cost_price,0) as cost_price," +
            " a.splite_num," +
            " a.order_id," +
            " a.order_item_id," +
            " a.combo_product_spec_id," +
            " a.split_product_spec_id," +
            " if(a.product_classify='ACCESSORY'," +
            "     ifnull(b.cost_price,0)," +
            "     if(a.splite_num=0 or c.cost_amount=0,0," +
            "       (a.sale_amount-c.accessory_amount)*(a.splite_num * ifnull(b.cost_price,0)/(c.cost_amount-c.accessory_amount))/a.splite_num)" +
            "       ) as splite_sale_price," +
            " a.admin_stock_out_id" +
            " from v_admin_order_splite a" +
            " left join admin_product_spec_stock b on a.split_product_spec_id = b.product_spec_id" +
            " left join (" +
            "        select a.order_item_id," +
            "                   sum(if(a.product_classify='ACCESSORY',ifnull(b.cost_price,0)*a.splite_num,0)) as accessory_amount," +
            "                   sum(ifnull(b.cost_price,0)*a.splite_num)     as cost_amount" +
            "        from v_admin_order_splite a" +
            "        left join admin_product_spec_stock b on a.split_product_spec_id = b.product_spec_id" +
            "        where a.admin_stock_out_id =?" +
            "        group by a.order_item_id" +
            " ) c on a.order_item_id = c.order_item_id" +
            " where a.admin_stock_out_id =?";
    public static void stockOutOrderItemSplite(long adminStockOutId){
        em().createNativeQuery(SQL_STOCK_OUT_ORDER_ITEM_SPLITE).setParameter(1,adminStockOutId).setParameter(2,adminStockOutId).executeUpdate();
    }

    /**
     * 写入出库历史记录
     * @param adminStockOutId
     */
    public final static String SQL_STOCK_OUT_HISTORY =
            "insert into admin_stock_history(" +
            " source_biz_type,source_biz_id,product_spec_id,opt_num,cost_price,cost_amount,former_cost_price,snap_stock_num,snap_stock_amount,create_at,source_biz_code" +
            ")" +
            " select 'STOCK_OUT' as source_biz_type," +
            "   a.admin_stock_out_id," +
            "   a.split_product_spec_id," +
            "   a.opt_num," +
            "   a.cost_price," +
            "   a.opt_num * a.cost_price as cost_amount," +
            "   a.cost_price as former_cost_price," +
            "   ifnull(b.stock_num,0) - a.opt_num as snap_stock_num," +
            "   (ifnull(b.stock_num,0) - a.opt_num)* a.cost_price as snap_stock_amount," +
            "   now() as create_at," +
            "   c.code" +
            " from (" +
            " select a.admin_stock_out_id," +
            "   split_product_spec_id," +
            "   sum(num) as opt_num," +
            "   max(cost_price)  as cost_price" +
            " from admin_order_item_splite a" +
            " where a.admin_stock_out_id = ?" +
            " group by a.admin_stock_out_id,a.split_product_spec_id" +
            ") a" +
            " left join admin_product_spec_stock b on a.split_product_spec_id = b.product_spec_id" +
            " left join admin_stock_out c on a.admin_stock_out_id = c.id";
    public static void stockOutHistory(long adminStockOutId){
        em().createNativeQuery(SQL_STOCK_OUT_HISTORY).setParameter(1,adminStockOutId).executeUpdate();
    }

    /**
     * 更新产品库存
     * @param adminStockOutId
     */
    public final static String SQL_STOCK_OUT_UPDATE_STOCK =
            "update (" +
            " select split_product_spec_id," +
            "   sum(num) as opt_num" +
            " from admin_order_item_splite a" +
            " where a.admin_stock_out_id = ?" +
            " group by a.split_product_spec_id" +
            ") a left join admin_product_spec_stock b on a.split_product_spec_id = b.product_spec_id" +
            " set b.stock_num = b.stock_num - a.opt_num";
    public static void stockOutUpdateStock(long adminStockOutId){
        em().createNativeQuery(SQL_STOCK_OUT_UPDATE_STOCK).setParameter(1,adminStockOutId).executeUpdate();
    }

    /**
     * 加载出库后的物料明细
     * @param adminStockOutId
     */
    public final static String SQL_LOAD_STOCK_OUT_SPLITE =
            "select b.d_code," +
            " c.name," +
            " b.spec," +
            " b.unit," +
            " a.opt_num as num," +
            " a.snap_stock_num + a.opt_num as stock_num," +
            " a.snap_stock_num as diff_num," +
            " case b.product_classify" +
            " when 'BASIC_PRODUCT' then '基础产品'" +
            " when 'FINISHED_PRODUCT' then '成品'" +
            " when 'ACCESSORY' then '辅料'" +
            " when 'COMBO_PRODUCT' then '套餐'" +
            " else '' end as product_classify" +
            " from admin_stock_history a" +
            " left join product_specs b on a.product_spec_id = b.id" +
            " left join products c on b.product_id = c.id" +
            " where a.source_biz_type = 'STOCK_OUT'" +
            " and a.source_biz_id = ?";
    public static List<Map<String,Object>> loadStockOutSplite(long adminStockOutId){
        Query query = em().createNativeQuery(SQL_LOAD_STOCK_OUT_SPLITE).setParameter(1,adminStockOutId);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.getResultList();
    }

    /**
     * 验证库存是否充足
     * @param adminStockOutId
     * @return
     */
    public final static String SQL_VALIDATE_STOCK_NUM =
            "select group_concat(d.name,'(',c.spec,')') from (" +
            "  select a.split_product_spec_id," +
            "  sum(a.splite_num) as splite_num" +
            "  from v_admin_order_splite a " +
            "  where a.admin_stock_out_id = ?" +
            "  group by a.split_product_spec_id" +
            " ) a left join admin_product_spec_stock b on a.split_product_spec_id = b.product_spec_id" +
            " left join product_specs c on a.split_product_spec_id = c.id" +
            " left join products d on c.product_id = d.id" +
            " where splite_num>b.stock_num";
    public static String validateStockNum(long adminStockOutId){
        String validateStr = ConvertUtil.toString(em().createNativeQuery(SQL_VALIDATE_STOCK_NUM).setParameter(1,adminStockOutId).getSingleResult());
        if(validateStr.length()>0)
            validateStr = validateStr.replace(",","<br>");
        return validateStr;
    }



















}
